# coding=utf-8
# 代码文件：chapter3/ch3.2.9.py
from datetime import date

import xlwings as xw

# 设置程序不可见运行
app = xw.App(visible=False, add_book=False)
f = r'data/学生信息.xlsx'
wb = app.books.open(f)

sheet1 = wb.sheets['Sheet1']
rng = sheet1.range('A1').current_region

# 遍历单元格
for cell in rng:

    if cell.address.startswith('$C'):  # 判断是否是第C列
        cell.number_format = 'yyyy-mm-dd'

    cell.font.size = 10
    cell.font.bold = True
    # 如果是成绩列设置数字格式
    if cell.address.startswith('$E'):  # 判断是否是第E列
        cell.number_format = '0.00'

    # 取出单元格数据
    data = cell.options(numbers=int, dates=date).value
    # 判断单元格是否为空值
    if data is None:
        cell.color = (255, 255, 0)  # 设置为黄色

# 保存修改后的文件另存为[文件学生信息2.xlsx]
f = r'data/学生信息2.xlsx'
wb.save(path=f)
wb.close()
app.quit()

print('Game Over')
